import operator
import time
from osv import osv, fields
from osv.orm import intersect
import tools.sql
from tools import config
from tools.translate import _
from mx import DateTime
import netsvc
from tools.amount_to_text import amount_to_text

class kderp_advance_payment(osv.osv):
    _name = 'kderp.advance.payment'
    _description = 'KDERP Advance Payment'
    
    def unlink(self, cr, uid, ids, context=None):
        if ids:
            payment_id = ",".join(map(str,ids))
            for rop in self.read(cr,uid,ids,['state']):
                if rop['state'] not in ('draft'):
                    raise osv.except_osv("KDERP Warning !","You can delete Advance Payment with status in Draft only")
        unlink_ids = ids
        return super(kderp_advance_payment, self).unlink(cr, uid, unlink_ids, context=context)
    
    def write(self, cr, uid, ids, vals, context=None):
        self.pool.get('ir.rule').domain_get.clear_cache(cr.dbname)
        return osv.osv.write(self, cr, uid, ids,vals,context=context)

    def fields_get(self, cr, uid, fields=None, context=None):
        res = super(kderp_advance_payment, self).fields_get(cr, uid, fields, context)
        new_field={'search_in_line_budget':
                                        {'widget':u'char',
                                         'type':u'char',
                                         'name':u'search_in_line_budget',
                                         'select': u'1',
                                         'string':u'Budget No',
                                         'invisible':True,
                                         'visible':False,
                                         'size':128,
                                         'sql':"(Select distinct adv_id from kderp_advance_payment_line kapl left join account_budget_post abp on budget_id=abp.id where abp.name || ' ' || abp.code ilike chr(37) || '%s' || chr(37))"}}
        res.update(new_field)
        return res
    
    def _exrate(self, cr, uid, ids, name, arg, context={}):
        res = {}
        if ids:
            cr.execute("Select id,\
                                fncompute(currency_id,get_default_currency(),date_order,'tax_office')\
                        from kderp_advance_payment where id in (%s) " % ",".join(map(str, ids)))
        for po_id, rate in cr.fetchall():
            res[po_id] = rate
        for obj_id in ids:
            res.setdefault(obj_id, 0.0)
        for id in ids:
            res[id] = res.get(id, 0.0)
        return res

    def _get_budgetcode_inline(self,cr,uid,ids,name,arg,context={}):
        res = {}
        if ids:
            po_ids = ",".join(map(str,ids))
            cr.execute("""Select \
                                adv.id,\
                                sum(code || ',') \
                        from\
                                kderp_advance_payment\
                        left join\
                                (Select \
                                        distinct order_id,\
                                        abp.code\
                                from\
                                        purchase_order_line pol\
                                left join\
                                        product_product pp on pol.product_id = pp.id\
                                left join\
                                        account_budget_post abp on kdvn_budget_id = abp.id) vwtemp on po.id = vwtemp.order_id\
                         where po.id in (%s) group by po.id""" % po_ids)
            budgets =  cr.fetchall()
            for id,budget in budgets:
                if budget:
                    tmp = budget.strip()
                    tmp = tmp[:len(tmp)-1]
                    res[id] = tmp
                else:
                    res[id] = budget            
        return res
    
    
    def _new_code_adv(self, cr, uid, context={}):
        new_code = False
        cr.execute("""Select
                         prefix || 
                         to_char(current_date,suffix) || 
                         lpad((max(substring(current_code from length(prefix ||suffix)+1 for padding)::integer)+1)::text,padding,'0') as new_code
                    from
                        (Select
                            case when 
                            po.name is null 
                            then 
                            prefix || to_char(current_date,suffix) || lpad('0',padding,'0')  else po.name end as current_code,
                            padding,
                            suffix,
                            prefix,current_date
                        from 
                            ir_sequence isq
                        left join
                            kderp_advance_payment po on po.name ilike prefix || to_char(current_date,suffix) || lpad('_',padding,'_')
                        where 
                            code='%s' and isq.active) vwcurrent_code
                    group by
                        padding,
                        suffix,
                        prefix,
                        current_date;""" %('action_advance_payment'))
        res = cr.fetchone()
        if res:
            new_code=res[0]            
        return new_code
    
    def _new_project_id(self, cr, uid, context):
        prj_id = False
        if context:
            if 'prj_context_id' in context:
                prj_id = context['prj_context_id']
        return prj_id

#Advanced Area
    
    def _get_total_advanced(self, cr, uid, ids, name, args, context={}):
        res = {}
        for po_obj in self.browse(cr,uid,ids):
            total_amount = 0.0
            for pol_obj in po_obj.advance_line_ids:
                total_amount+= (pol_obj.amount or 0.0) + (pol_obj.vat_amount or 0.0)
            #raise osv.except_osv("E",amount_to_text(total_amount,'in',po_obj.currency_id.spell).upper())
            res[po_obj.id]={'advance_amount':total_amount,
                            'in_word':amount_to_text(total_amount,'in',po_obj.currency_id.spell).upper()}
        return res
    
    def _get_total_advanced_details(self, cr, uid, ids, name, args, context={}):
        res = {}
        for po_obj in self.browse(cr,uid,ids):
            actual_total_amount = 0.0
            for al in po_obj.detail_advance_ids:
                actual_total_amount= actual_total_amount + (al.amount or 0.0) + (al.vat_amount or 0.0)
            res[po_obj.id]=actual_total_amount
        return res
    
    def _get_balance(self, cr, uid, ids, name, args, context={}):
        res = {}
        for po_obj in self.browse(cr,uid,ids):
            res[po_obj.id]=po_obj.advance_amount - po_obj.actual_advance_amount
            
        return res
    
    def _get_adv_line(self, cr, uid, ids, context={}):
        result = {}
        for line in self.pool.get('kderp.advance.payment.line').browse(cr, uid, ids, context=context):
            result[line.adv_id.id] = True
        return result.keys()
    
    def action_draft_advanced(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state':'waiting'})
        return True
    
    def action_adv_cancel(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state':'cancel'})
        return True
    
    def action_adv_revise(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state':'draft'})
        return True
    
    def action_adv_open(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state':'draft'})
        return True
    
    def action_advanced_released(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state':'done'})
        return True

    _columns={
    
        #'budget_codes':fields.function(_get_budgetcode_inline,method=True,type='char',size=250,string='Budget'),
        'exrate':fields.function(_exrate,method=True,string="Ex.Rate",type='float'),
        'name': fields.char('Adv. No.', size=64, required=True, select=True,states={'done':[('readonly',True)], 'cancel':[('readonly',True)]}),
        'date_order':fields.date('Date', required=True, states={'done':[('readonly',True)], 'cancel':[('readonly',True)]}),
        'receipts_date':fields.date('Receipt Date', states={'done':[('readonly',True)], 'cancel':[('readonly',True)]},select=1),
        'user_id': fields.many2one('res.users', 'User in Charge',states={'done':[('readonly',True)], 'cancel':[('readonly',True)]}),

        'advance_line_ids': fields.one2many('kderp.advance.payment.line', 'adv_id', 'Adv Lines', states={'done':[('readonly',True)], 'cancel':[('readonly',True)]}),
        'detail_advance_ids':fields.one2many('kderp.detail.of.advance','adv_id','Advance Detail',states={'done':[('readonly',True)], 'cancel':[('readonly',True)]}),
        
        'project_id':fields.many2one('kdvn.project','Project',states={'done':[('readonly',True)], 'cancel':[('readonly',True)]},select=1),
     
        'state':fields.selection([('draft','Draft'),('cancel','Cancel'),('waiting','Advanced'),('done','Released')],'Status',readonly='True'),

        'currency_id':fields.many2one('kderp.currency','Cur.',required=True,states={'done':[('readonly',True)], 'cancel':[('readonly',True)]},context={'typeofrate':'tax_office'}),
        
        'notes': fields.text('Description',states={'done':[('readonly',True)], 'cancel':[('readonly',True)]}),
       
        'advance_amount':fields.function(_get_total_advanced,string='Adv. Amount',type='float',method=True,multi='_get_advance_from_line',
                                         store={
                                                'kderp.advance.payment.line': (_get_adv_line, None, 10),
                                                'kderp.advance.payment':(lambda self, cr, uid, ids, c={}: ids, None, 20)}),
        'in_word':fields.function(_get_total_advanced,string='In Word',type='char',size=256,method=True,multi='_get_advance_from_line',store={
                                                'kderp.advance.payment.line': (_get_adv_line, None, 10),
                                                'kderp.advance.payment':(lambda self, cr, uid, ids, c={}: ids, None, 20)}),
              
        'actual_advance_amount':fields.function(_get_total_advanced_details,string='Acutal Amount',type='float',method=True),
        'balance':fields.function(_get_balance,string='Balance',type='float',method=True),
        }
    _sql_constraints = [('adv_number_unique', 'unique (name)', 'KDERP Error: The number of the Advance must be unique !')]
    
    
    _order = "name"    
    _defaults={
              'name':_new_code_adv,
              'project_id':_new_project_id,
              'state':lambda *x:'draft',
              'date_order':lambda *x:time.strftime('%Y-%m-%d %H:%M:%S')
              }
    
    def copy(self, cr, uid, id, default=None,context={}):
        if not default:
            default = {}
            
        default.update({
            'state':'draft',
            'name':self._new_code_adv(cr,uid)
        })
        
        new_id= super(kderp_advance_payment, self).copy(cr, uid, id, default, context)
        self.pool.get('ir.rule').domain_get.clear_cache(cr.dbname)
        return new_id
kderp_advance_payment()

class kderp_advance_payment_line(osv.osv):
    _name = 'kderp.advance.payment.line'
    _description = 'KDERP Advance Payment Line'

    _columns={
            'budget_id': fields.many2one('account.budget.post', 'Budget',required=True),
            'name':fields.char("Description",size=256),
            'amount': fields.float('Amount',required=True),
            'vat_amount': fields.float('Amount'),
            'adv_id': fields.many2one('kderp.advance.payment', 'Order Ref', required=True, ondelete='cascade'),
        }
   # _sql_constraints = [('budget_doesnot_exist_adv_line', 'CHECK (fn_check_constraint_project_budget_advance(budget_id,adv_id))',  'KDERP Error: The budget code of the Job does not exists !')]
    _defaults = {
        'amount':lambda *x:0,
        }
kderp_advance_payment_line()


class kderp_detail_of_advance(osv.osv):
    _name='kderp.detail.of.advance'
    _description='KDERP Detail of Advance'
    
    def new_budget_id(self, cr, uid, context={}):
        budget_id = False
        if context:
            if 'order_id' in context:
                order_id = context['order_id']
                cr.execute("Select budget_id from kderp_detail_of_advance pol where adv_id=%s limit 1" % order_id)
                #project_id = self.pool.get('purchase.order.line').read(cr,uid,[order_id],['project_id'])[0]
                new_product_obj = cr.fetchone()
                budget_id =False
                if new_product_obj:
                    budget_id = new_product_obj[0]
        return budget_id
    
    _columns = {
                'budget_id': fields.many2one('account.budget.post', 'Budget'),#change_default=True
                'quantity':fields.integer('Qty.'),
                'unit_id': fields.many2one('product.uom', 'Unit'),
                'amount':fields.float('Amount', required=True),
                'name': fields.char('Description', size=120, required=True),
                'adv_id':fields.many2one('kderp.advance.payment','Adv.'),
                'vat_amount':fields.float("VAT Amount"),
                }
    _defaults = {
        'budget_id':new_budget_id,
        'amount':lambda *x:0,
        'quantity':lambda *x:1,
        }
    #_sql_constraints = [('budget_doesnot_exist_adv_detail', 'CHECK (fn_check_constraint_project_budget_advance(budget_id,adv_id))',  'KDERP Error: The budget code of the Job does not exists !')]
    def _amount_change(self,cr,uid,ids,amount):
        res={'value':{'vat_amount':amount*0}}
        return res
kderp_detail_of_advance()

class kderp_advance_for_budget_summary(osv.osv):
    _description='KDERP Advance for Budget Summary'
    _name = 'kderp.advance.for.budget.summary'
    _auto = False
    
    _columns = {
                'adv_id':fields.many2one('kderp.advance.payment','Adv. No.'),
                'description':fields.text("Description"),
                'advanced_amount':fields.float("Adv. Amount"),
                'amount_in_idr':fields.float("Adv. Amount (In Rp.)"),
                }
    def init(self,cr):
        cr.execute("""create or replace view kderp_advance_for_budget_summary as
                    Select
                        row_number() over (order by adv_id,project_id,budget_id) as id,
                        *
                    from
                        (Select
                            kap.id as adv_id,
                            kap.project_id,
                            budget_id,
                            notes as description,
                            sum(kapl.amount) as advanced_amount,
                            sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*kapl.amount) as amount_in_idr
                        from
                            kderp_advance_payment kap
                        left join
                            kderp_advance_payment_line kapl on kap.id = adv_id
                        where
                            state not in ('draft','cancel') and
                            not exists(Select id from kderp_detail_of_advance kdoa where kdoa.adv_id= kap.id)
                        Group by
                            kap.id,
                            budget_id
                        union all
                        Select
                            kap.id as adv_id,
                            kap.project_id,
                            budget_id,
                            notes as description,
                            sum(kdoa.amount) as advanced_amount,
                            sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*kdoa.amount) as amount_in_idr                            
                        from
                            kderp_advance_payment kap
                        left join
                            kderp_detail_of_advance kdoa on kap.id = kdoa.adv_id
                        where
                            state not in ('draft','cancel') and kdoa.id >0
                        Group by
                            kap.id,
                            budget_id) vwcombined""")
kderp_advance_for_budget_summary()